package com.xl.bigdata.tools.mysql;

import com.xl.bigdata.photo.bean.PhotoHeartBean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class MysqlClient {

//	mysql.url=jdbc:mysql://10.155.6.102:3306/phone?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
//	mysql.driver=com.mysql.jdbc.Driver
//	mysql.username=root
//	mysql.password=root

    private static String driver = "com.mysql.jdbc.Driver";

    private static String url = "jdbc:mysql://10.155.20.61:3306/letu?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE";

    private static String username = "root";

    private static String password = "root";

    private static Connection conn;

    public MysqlClient() {

    }

    public static void init() {
        try {

//			driver = Config.MysqlDriver;
//			url = Config.MysqlUrl;
//			username = Config.MysqlUserName;
//			password = Config.MysqlPassword;

            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static synchronized Connection getConnection() {

        try {
            if (conn == null || conn.isClosed()) {
                try {
                    conn = (Connection) DriverManager.getConnection(url, username, password);
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;
    }

    public static void insertBatch(Connection con, List<Object> list) {

        long start = System.currentTimeMillis();

        String sql = "INSERT INTO phone_info (phone, date, content) VALUES (?, ?, ?)";

        PreparedStatement psts = null;
        try {
            con.setAutoCommit(false);

            int count = 0;
            psts = con.prepareStatement(sql);

            con.commit(); // 提交

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (psts != null) {
                    psts.close();
                }

                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        long end = System.currentTimeMillis();

        System.out.println("MysqlClient - 向mysql-phone表写入数据量 ： " + list.size() + "  花费的时间：" + (end - start));
    }

    public static PhotoHeartBean searchPhotoHeart(String path, String ip, Connection con) {

        StringBuffer sb = new StringBuffer();
        sb.append("select * from photo_heart where path= '");
        sb.append(path);
        sb.append("' and ip = '");
        sb.append(ip);
        sb.append("';");
        PhotoHeartBean photoHeartBean = new PhotoHeartBean();
        photoHeartBean.setId(-1);
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sb.toString());
            if (rs.next()) {
                photoHeartBean.setIsStop(rs.getString("isStop"));
                photoHeartBean.setId(rs.getLong("id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return photoHeartBean;
    }

    public static void updatePhotoHeart(PhotoHeartBean photoHeartBean, Connection con) {

        String sql = "update photo_heart set heartTime = ?, recCount = ?, heartPeriod = ?   where id= ?";

        try {

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1,photoHeartBean.getHeartTime());
            ps.setLong(2,photoHeartBean.getRecCount());
            ps.setString(3,photoHeartBean.getHeartPeriod());
            ps.setLong(4,photoHeartBean.getId());
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void updatePhotoHeartStatus(PhotoHeartBean photoHeartBean, Connection con) {

        String sql = "update photo_heart set status = ? where id= ?";

        try {

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"stop");
            ps.setLong(2,photoHeartBean.getId());
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void insertPhotoHeart(PhotoHeartBean photoHeartBean, Connection con) {



        String sql = "INSERT INTO photo_heart " +
                "(ip, path, isStop, recCount, heartTime, heartPeriod, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        PreparedStatement psts = null;
        try {
            psts = con.prepareStatement(sql);
            psts.setString(1, photoHeartBean.getIp());
            psts.setString(2, photoHeartBean.getPath());
            psts.setString(3, photoHeartBean.getIsStop());
            psts.setLong(4,photoHeartBean.getRecCount());
            psts.setLong(5,photoHeartBean.getHeartTime());
            psts.setString(6, photoHeartBean.getHeartPeriod());
            psts.setString(7, photoHeartBean.getStatus());
            psts.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (psts != null) {
                    psts.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    public static void insertCount(Connection con, int count, String date, String desc) {

        long start = System.currentTimeMillis();

        String sql = "INSERT INTO phone_count (date, count, remark) VALUES (?, ?, ?)";

        PreparedStatement psts = null;
        try {
            psts = con.prepareStatement(sql);
            psts.setString(1, date);
            psts.setInt(2, count);
            psts.setString(3, desc);
            psts.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (psts != null) {
                    psts.close();
                }

                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        long end = System.currentTimeMillis();

        System.out.println("MysqlClient - 向mysql-phone表写入数据量 ： date :" + date + "  count : " + count + "  花费的时间：" + (end - start));
    }

    public static void main(String[] args) throws SQLException {

        // TODO Auto-generated method stub

//		Config.MysqlDriver = "com.mysql.jdbc.Driver";
//		Config.MysqlUrl = "jdbc:mysql://10.155.6.102:3306/phone?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE";
//		Config.MysqlUserName = "root";
//		Config.MysqlPassword = "root";

        // MysqlClient - 向mysql-phone表写入数据量 ： 1607806 花费的时间：642456
        // MysqlClient - 10000 向mysql-phone表写入数据量 ： 1488851 花费的时间：507285
        // MysqlClient - 15000 向mysql-phone表写入数据量 ： 1488851 花费的时间：508987
        // MysqlClient - 1000 向mysql-phone表写入数据量 ： 1488851 花费的时间：510364
        // MysqlClient - 向mysql-phone表写入数据量 ： 1488851 花费的时间：511792
        // MysqlClient - 向mysql-phone表写入数据量 ： 1305774 花费的时间：461161
        // MysqlClient - 向mysql-phone表写入数据量 ： 1607806 花费的时间：643308

//		List<PhoneBean> list = CsvUtil.readCSV("D:\\z", "D:\\A");

        init();
        Connection con = MysqlClient.getConnection();
//		insertBatch(con, list);

    }

}
